package edu.rit.varapp.datalayer;

import edu.rit.varapp.db.DatabaseConnection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import static edu.rit.varapp.utilities.StringUtils.*;

/**
 * ModuleOps provides methods to perform various functions on the database in relation to Module objects.
 */
public class ModuleOps {

    private DatabaseConnection conn;

    public ModuleOps() {
        this.conn = DatabaseConnection.instance();
    }

    /**
     * Inserts a new module in the database.
     * @param module The module information that we want to insert.
     * @return True if information has been successfully written, false otherwise.
     * @throws SQLException
     */
    public boolean insertModule( Module module ) throws SQLException {
        String sql = "INSERT INTO modules (" + Module.columnsToSQL() + ") VALUES (" + module.valuesToSQL( true ) + ")";
        int val = conn.executeUpdateQuery( sql );
        if( val > 0 ) {
            return true;
        }
        return false;
    }

    /**
     * Deletes a module from the database.
     * @param module The module to delete.
     * @return True if the information has been successfully deleted, false otherwise.
     * @throws SQLException
     */
    public boolean deleteModule( Module module ) throws SQLException {
        String sql = "DELETE FROM modules WHERE SerialNumber = " + module.getSerialNumber();
        int val = conn.executeUpdateQuery( sql );
        if( val > 0 ) {
            return true;
        }
        return false;
    }

    /**
     * Updates a module with the given information. This is done by deleting the existing module, and inserting
     * a "new" module.
     * @param module The existing module to update.
     * @return True if the module has been updated correctly, false otherwise.
     * @throws SQLException
     */
    public boolean updateModule( Module oldModule, Module newModule) throws SQLException {
        try {
            conn.setAutoCommit( false );
            if( deleteModule( oldModule ) ) {
                if( insertModule( newModule ) ) {
                    conn.setAutoCommit( true );
                    return true;
                }
            }
        } catch( SQLException sqle ) {
            conn.rollback();
            throw sqle;
        }
        return false;
    }

    /**
     * Retrieves all existing modules for a certain customer. This is just a convenience method when we're dealing
     * with full Customer objects. Otherwise, just pass the information down.
     * @param customer The customer we want to retrieve modules for.
     * @return The collection of current modules for the customer.
     * @see #retrieveModules(int)
     */
    public ArrayList<Module> retrieveModules( Customer customer ) throws SQLException {
        return retrieveModules( customer.getEntityId() );
    }

    /**
     * Retrieves all existing modules for a customer.
     * @param entityId The customer's entityId.
     * @return The collection of modules for the customer.
     * @throws SQLException
     */
    public ArrayList<Module> retrieveModules( int entityId ) throws SQLException {
        ArrayList<Module> ret = new ArrayList<Module>();
        String sql = "SELECT " + Module.columnsToSQL() + " FROM modules JOIN customers USING (EntityId)"
                     + " WHERE EntityId = " + entityId;
        ResultSet rs = conn.executeQuery( sql );
        ArrayList<ArrayList<String>> data = conn.getData( rs, false );

        for( ArrayList<String> row : data ) {
            ret.add( buildModule( row ) );
        }
        return ret;
    }

    /**
     * Returns the last serialNumber + 1. This serialNumber will be given to a new module as a holder for when the VAR
     * receives the modules' "real" serialNumber from the software manufacturer.
     * @return An unused serialNumber.
     * @throws SQLException
     */
    public synchronized int newSerialNumber() throws SQLException {
        int ret = -1;
        String sql = "SELECT SerialNumber FROM modules ORDER BY SerialNumber DESC LIMIT 0,1";
        ResultSet rs = conn.executeQuery( sql );
        if( rs.next() ) {
            ret = rs.getInt( "SerialNumber" ) + 1;
        }
        return ret;
    }

    /**
     * Retrieves a single Module's information based on its serial number.
     * @param serialNumber The module's serial number.
     * @return A Module object that has the serial number or null if there is no such module.
     * @throws SQLException
     */
    public Module retrieveModule( int serialNumber ) throws SQLException {
        String sql = "SELECT " + Module.columnsToSQL() + " FROM modules WHERE SerialNumber = " + serialNumber;
        ResultSet rs = conn.executeQuery( sql );
        if( rs.next() ) {
            return buildModule( conn.getSingleRow( rs ) );
        }
        return null;
    }

    /**
     * Builds a Module object from a row of data.
     */
    private Module buildModule( ArrayList<String> data ) {
        return new Module( Integer.parseInt( data.get( 0 ) ),
                           Integer.parseInt( data.get( 1 ) ),
                           data.get( 2 ),
                           data.get( 3 ),
                           data.get( 4 ),
                           (!checkNull( data.get( 5 ) )) ? Integer.parseInt( data.get( 5 ) ) : 0,
                           (!checkNull( data.get( 6 ) )) ? Integer.parseInt( data.get( 6 ) ) : 0,
                           (!checkNull( data.get( 7 ) )) ? Integer.parseInt( data.get( 7 ) ) : 0,
                           (!checkNull( data.get( 8 ) )) ? Integer.parseInt( data.get( 8 ) ) : 0,
                           (!checkNull( data.get( 9 ) )) ? Integer.parseInt( data.get( 9 ) ) : 0,
                           (!checkNull( data.get( 10 ) )) ? Integer.parseInt( data.get( 10 ) ) : 0,
                           (!checkNull( data.get( 11 ) )) ? Integer.parseInt( data.get( 11 ) ) : 0,
                           (!checkNull( data.get( 12 ) )) ? checkBoolean(data.get( 12 ) ) : false,
                           (!checkNull( data.get( 13 ) )) ? checkBoolean( data.get( 13 ) ) : false,
                           (!checkNull( data.get( 14 ) )) ? checkBoolean( data.get( 14 ) ) : false,
                           data.get( 15 ),
                           data.get( 16 ),
                           data.get( 17 ) );
    }
}
